package com;


import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.joda.time.DateTime;
import org.junit.jupiter.api.Test;

import java.io.FileInputStream;
import java.util.Date;

public class ExcelReadTest {
    final String PATH ="D:\\JAVA_WORKSPACE\\JAVA_SE\\javalesson\\easyExcel\\src\\main\\java\\com\\";
    @Test
    public void testRead() throws Exception {
        //得到文件读取的流
        FileInputStream fileInputStream = new FileInputStream(PATH + "公式.xlsx");
        //创建一个工作薄，保存数据，需要根据后缀选择方法
        XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
        //获得第一张表
        XSSFSheet sheet = workbook.getSheetAt(0);
        XSSFRow rowTitle = sheet.getRow(0);
        if (rowTitle != null) {
            //获取行数
            int cellCount = rowTitle.getPhysicalNumberOfCells();
            for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                XSSFCell cell = rowTitle.getCell(cellNum);
                if (cell != null) {
                    CellType cellType = cell.getCellType();
                    String value = cell.getStringCellValue();
                    System.out.println(value + "|");
                }
            }
        }
        int rows = sheet.getPhysicalNumberOfRows();
        for (int rowNum = 1; rowNum<rows;rowNum++){
            XSSFRow rowData = sheet.getRow(rowNum);
            if (rowData != null) {
                int cellCount = rowTitle.getPhysicalNumberOfCells();
                for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                    System.out.println("["+(rowNum+1)+"]"+"-"+(cellNum+1)+"]");
                    XSSFCell cell = rowData.getCell(cellNum);
                    if (cell != null) {
                        CellType cellType = cell.getCellType();
                        String cellValue = "";
                        switch (cellType) {
                            case STRING:
                                System.out.println("[String]");
                                cellValue = cell.getStringCellValue();
                                break;
                            case _NONE:
                                break;
                            case NUMERIC:
                                System.out.println("[NUMERIC]");
                                if (DateUtil.isCellDateFormatted(cell)) {
                                    System.out.println("日期");
                                    Date dateCellValue = cell.getDateCellValue();
                                    cellValue=new DateTime(dateCellValue).toString("yyyy-MM-dd");
                                }else {
                                    System.out.println("转换为字符串输出");
                                    cell.setCellType(CellType.STRING);
                                    cellValue=cell.toString();
                                }
                                break;
                            case FORMULA:
                                cellValue = cell.getStringCellValue();
                                System.out.println("[FORMULA]");
                                break;
                            case BLANK:
                                System.out.println("[BLANK]");
                                cellValue= cell.getStringCellValue();
                                break;
                            case BOOLEAN:
                                System.out.println("[BOOLEAN]");
                                cellValue = String.valueOf(cell.getBooleanCellValue());
                                break;
                            case ERROR:
                                System.out.println("数据类型错误");
                                break;
                        }
                        System.out.println(cellValue);
                    }
                }
            }
        }
        //读取值的时候要进行数据的判断
        fileInputStream.close();
    }
    @Test
    public void testFromula() throws Exception {
        FileInputStream in = new FileInputStream(PATH+"公式.xlsx");
        XSSFWorkbook sheets = new XSSFWorkbook(in);
        XSSFSheet sheetAt = sheets.getSheetAt(0);
        XSSFRow row = sheetAt.getRow(3);
        XSSFCell cell = row.getCell(0);
        XSSFFormulaEvaluator xssfFormulaEvaluator = new XSSFFormulaEvaluator(sheets);
        //计算拿到的公式
        CellType cellType = cell.getCellType();
        switch (cellType) {
            case FORMULA://公式
                String cellFormula = cell.getCellFormula();
                System.out.println(cellFormula);
                //计算
                CellValue evaluate = xssfFormulaEvaluator.evaluate(cell);
                String cellvalue = evaluate.formatAsString();
                System.out.println(cellvalue);
                break;
        }
    }
}
